--====================================
-- Create table of REGIONS
--====================================
create table REGIONS
(
  REGION_ID   NUMBER,
  REGION_NAME VARCHAR2(25)
); 
-- Create/Recreate primary, unique and foreign key constraints 
alter table REGIONS
  add constraint REG_ID_PK primary key (REGION_ID);
-- Create/Recreate check constraints 
alter table REGIONS
  add constraint REGION_ID_NN
  check ("REGION_ID" IS NOT NULL);  
  
  
--=====================================
---Create table of COUNTRIES
--=====================================
create table COUNTRIES
(
  COUNTRY_ID   CHAR(2),
  COUNTRY_NAME VARCHAR2(40),
  REGION_ID    NUMBER,
  constraint COUNTRY_C_ID_PK primary key (COUNTRY_ID)
) ;
 
alter table COUNTRIES
  add constraint COUNTR_REG_FK foreign key (REGION_ID)
  references REGIONS (REGION_ID);
-- Create/Recreate check constraints 
alter table COUNTRIES
  add constraint COUNTRY_ID_NN
  check ("COUNTRY_ID" IS NOT NULL);
  
  
--=====================================
---Create table of LOCATIONS
--=====================================
create table LOCATIONS
(
  LOCATION_ID    NUMBER(4) not null,
  STREET_ADDRESS VARCHAR2(40),
  POSTAL_CODE    VARCHAR2(12),
  CITY           VARCHAR2(30),
  STATE_PROVINCE VARCHAR2(25),
  COUNTRY_ID     CHAR(2)
);  
-- Create/Recreate primary, unique and foreign key constraints 
alter table LOCATIONS
  add constraint LOC_ID_PK primary key (LOCATION_ID);
 
alter table LOCATIONS
  add constraint LOC_C_ID_FK foreign key (COUNTRY_ID)
  references COUNTRIES (COUNTRY_ID);
-- Create/Recreate check constraints 
alter table LOCATIONS
  add constraint LOC_CITY_NN
  check ("CITY" IS NOT NULL);
-- Create/Recreate indexes 
create index LOC_CITY_IX on LOCATIONS (CITY);
   
create index LOC_COUNTRY_IX on LOCATIONS (COUNTRY_ID);
 
create index LOC_STATE_PROVINCE_IX on LOCATIONS (STATE_PROVINCE);
 

  
  
